SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE static_search_7
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @ishistoric  bit          = 0, 
       @sortcol1    varchar(20)  = 'OrderID',
       @isdesc1     bit          = 0,
       @sortcol2    varchar(20)  = 'ProductID',
       @isdesc2     bit          = 0 AS

SELECT u.OrderID, u.OrderDate, u.UnitPrice, u.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, u.EmployeeID
FROM   (SELECT o.OrderID, o.Status, o.OrderDate, od.UnitPrice,
               od.Quantity, o.CustomerID, od.ProductID, o.EmployeeID
        FROM   Orders o
        JOIN   [Order Details] od ON o.OrderID = od.OrderID
        WHERE  @ishistoric = 0
        UNION  ALL
        SELECT ho.OrderID, ho.Status, ho.OrderDate, hod.UnitPrice,
               hod.Quantity, ho.CustomerID, hod.ProductID, ho.EmployeeID
        FROM   HistOrders ho
        JOIN   HistOrderDetails hod ON ho.OrderID = hod.OrderID
        WHERE  @ishistoric = 1) AS u
JOIN   Customers c ON c.CustomerID = u.CustomerID
JOIN   Products p ON p.ProductID = u.ProductID
WHERE  (u.OrderID = @orderid OR @orderid IS NULL)
  AND  (u.Status = @status OR @status IS NULL)
  AND  (u.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (u.OrderDate <= @todate OR @todate IS NULL)
  AND  (u.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (u.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY
   CASE WHEN @isdesc1 = 0 THEN
           CASE @sortcol1 WHEN 'OrderID'    THEN u.OrderID
                          WHEN 'EmployeeID' THEN u.EmployeeID
                          WHEN 'ProductID'  THEN u.ProductID
           END
   END  ASC,
   CASE WHEN @isdesc1 = 1 THEN
           CASE @sortcol1 WHEN 'OrderID'    THEN u.OrderID
                          WHEN 'EmployeeID' THEN u.EmployeeID
                          WHEN 'ProductID'  THEN u.ProductID
           END
   END  DESC,
   CASE WHEN @isdesc1 = 0 THEN
         CASE @sortcol1 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END ASC,
   CASE WHEN @isdesc1 = 1 THEN
         CASE @sortcol1 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END DESC,
   CASE WHEN @isdesc1 = 0 AND @sortcol1 = 'OrderDate' THEN u.OrderDate
   END ASC,
   CASE WHEN @isdesc1 = 1 AND @sortcol1 = 'OrderDate' THEN u.OrderDate
   END DESC,
   CASE WHEN @isdesc2 = 0 THEN
           CASE @sortcol2 WHEN 'OrderID'    THEN u.OrderID
                          WHEN 'EmployeeID' THEN u.EmployeeID
                          WHEN 'ProductID'  THEN u.ProductID
           END
   END  ASC,
   CASE WHEN @isdesc2 = 1 THEN
           CASE @sortcol2 WHEN 'OrderID'    THEN u.OrderID
                          WHEN 'EmployeeID' THEN u.EmployeeID
                          WHEN 'ProductID'  THEN u.ProductID
           END
   END  DESC,
   CASE WHEN @isdesc2 = 0 THEN
         CASE @sortcol2 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END ASC,
   CASE WHEN @isdesc2 = 1 THEN
         CASE @sortcol2 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END DESC,
   CASE WHEN @isdesc2 = 0 AND @sortcol2 = 'OrderDate' THEN u.OrderDate
   END ASC,
   CASE WHEN @isdesc2 = 1 AND @sortcol2 = 'OrderDate' THEN u.OrderDate
   END DESC
OPTION (RECOMPILE)
go
-- Test cases.
EXEC static_search_7 @custid = 'BERGS', @ishistoric = 0,
     @sortcol1 = 'OrderDate', @isdesc1 = 1 
EXEC static_search_7 @custid = 'BERGS', @ishistoric = 1,
     @sortcol1 = 'ProductName', @sortcol2 = 'OrderID'